package it.blog.server;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;

class DataAccess implements IDataAccess {

	private IConnection connectionManager;
	private static DataAccess instance = null;

	private DataAccess() {
		connectionManager = new ConnectionManager();
	}

	public static DataAccess getInstance() {
		if (instance == null)
			instance = new DataAccess();
		return instance;
	}

	public DataAccess(String driver, String url, String user, String password,
			int numberConnections) {
		this.connectionManager = new ConnectionManager(driver, url, user,
				password, numberConnections);
	}

	public IConnection getConnectionManager() {
		return connectionManager;
	}

	@Override
	public User getUser(Integer userID) throws SQLException {

		User user = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn.prepareStatement("SELECT * FROM users WHERE userID=?;");
		stm.setInt(1, userID);

		result = stm.executeQuery();

		if (result.first()) {
			user = new User();

			user.userID = result.getInt("userID");
			user.name = result.getString("name");
			user.surname = result.getString("surname");
			user.gender = result.getBoolean("gender");
						
			Calendar c = Calendar.getInstance();
			c.setTime(result.getDate("dateOfBirth"));
			user.dateOfBirth = c;
					
			user.email = result.getString("email");
			user.username = result.getString("username");
			user.password = null;
			user.state = result.getBoolean("state");
			user.role = result.getInt("role");
			user.siteURL = result.getString("siteURL");
			user.blogURL = result.getString("blogURL");
			user.imgURL = result.getString("imgURL");
		}
		connectionManager.recycleConnection(conn);
		return user;
	}

	@Override
	public List<Blog> getBlogList(String order, Boolean descending) throws SQLException {

		List<Blog> blogs = null;
		Blog blog = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		String ascdesc;
		if(descending)
			ascdesc="DESC";
		else
			ascdesc="ASC";
		stm = conn.prepareStatement("SELECT * FROM blogs ORDER BY "+order+" "+ascdesc+";");
		result = stm.executeQuery();

		if (result.first()) {
			blogs = new ArrayList<Blog>();
			result.beforeFirst();
			while (result.next()) {
				blog = new Blog();
				blog.blogID = result.getInt("id");
				blog.blogTitle = result.getString("blogTitle");
				blog.blogOwner = result.getInt("blogOwner");
				blog.blogCategory = result.getInt("blogCategory");
				blog.counterBlogView = result.getInt("counterBlogView");
				blog.imgURL=result.getString("imgURL");
				blogs.add(blog);
			}
		}
		connectionManager.recycleConnection(conn);
		return blogs;
	}

	@Override
	public List<BlogCategory> getBlogCategoryList() throws SQLException {

		List<BlogCategory> blogCategories = null;
		BlogCategory blogCat = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn.prepareStatement("SELECT * FROM blogcategories ORDER BY name ASC;");
		result = stm.executeQuery();

		if(result.first()){
			blogCategories = new ArrayList<BlogCategory>();
			result.beforeFirst();
			while (result.next()) {
				blogCat = new BlogCategory();
				blogCat.id = result.getInt("id");
				blogCat.name = result.getString("name");
				blogCat.desc = result.getString("descr");
				blogCategories.add(blogCat);
			}
		}
		connectionManager.recycleConnection(conn);
		return blogCategories;
	}

	@Override
	public Blog getBlog(Integer blogID) throws SQLException {

		Blog blog = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("SELECT blogs.* FROM blogs WHERE blogs.id = ?;");
		stm.setInt(1, blogID);
		result = stm.executeQuery();

		if (result.first()) {
			blog = new Blog();
			blog.blogID = result.getInt("id");
			blog.blogTitle = result.getString("blogTitle");
			blog.blogOwner = result.getInt("blogOwner");
			blog.blogCategory = result.getInt("blogCategory");
			blog.counterBlogView = result.getInt("counterBlogView");
			blog.imgURL=result.getString("imgURL");
		}
		connectionManager.recycleConnection(conn);
		return blog;
	}
	
	@Override
	public Blog getBlogOfUser(Integer userID) throws SQLException {

		Blog blog = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("SELECT blogs.* FROM blogs WHERE blogs.blogOwner = ?;");
		stm.setInt(1, userID);
		result = stm.executeQuery();

		if (result.first()) {
			blog = new Blog();
			blog.blogID = result.getInt("id");
			blog.blogTitle = result.getString("blogTitle");
			blog.blogOwner = result.getInt("blogOwner");
			blog.blogCategory = result.getInt("blogCategory");
			blog.counterBlogView = result.getInt("counterBlogView");
			blog.imgURL=result.getString("imgURL");
		}
		connectionManager.recycleConnection(conn);
		return blog;
	}

	@Override
	public Post getPost(Integer postID) throws SQLException {
		Post post = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("SELECT posts.* FROM posts WHERE posts.id = ?;");
		stm.setInt(1, postID);
		result = stm.executeQuery();

		if (result.first()) {
			post = new Post();
			post.postID = result.getInt("id");
			post.blogID = result.getInt("blogID");
			post.title = result.getString("title");
			post.content = result.getString("content");
			post.writer = result.getInt("Writer");
			post.imgURL = result.getString("imgURL");
			post.category = result.getInt("category");
			
			Calendar c = Calendar.getInstance();
			c.setTime(result.getTimestamp("addDate"));
			post.date = c;
			
			post.counterPostView = result.getInt("counterPostView");
		}
		connectionManager.recycleConnection(conn);
		return post;
	}

	@Override
	public List<Post> getPostsOfBlog(Integer blogID, String order, Boolean descending) throws SQLException {

		List<Post> posts = null;
		Post aPost = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		String ascdesc;
		if(descending)
			ascdesc="DESC";
		else
			ascdesc="ASC";
		
			stm = conn.prepareStatement("SELECT * FROM posts WHERE blogID = ? ORDER BY "+order+" "+ascdesc+";");

		stm.setInt(1, blogID);
		result = stm.executeQuery();
		
		if(result.first()) {
			posts = new ArrayList<Post>();
			result.beforeFirst();
			while(result.next()){
				aPost = new Post();
				aPost.postID = result.getInt("id");
				aPost.blogID = result.getInt("blogID");
				aPost.title = result.getString("title");
				aPost.content = result.getString("content");
				aPost.writer = result.getInt("Writer");
				aPost.imgURL = result.getString("imgURL");
				aPost.category = result.getInt("category");
				
				Calendar c = Calendar.getInstance();
				c.setTime(result.getTimestamp("addDate"));
				aPost.date = c;
				
				aPost.counterPostView = result.getInt("counterPostView");
				
				posts.add(aPost);
				
			}	
			
		}

		connectionManager.recycleConnection(conn);
		return posts;
	}

	@Override
	public List<Comment> getCommentsOfPost(Integer blogID, Integer postID, String order,  Boolean descending)	throws SQLException {
		List<Comment> comments = null;
		Comment comment = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		String ascdesc;
		if(descending)
			ascdesc="DESC";
		else
			ascdesc="ASC";
		stm = conn
		.prepareStatement("SELECT * FROM comments WHERE (blogID = ?) AND ( postID = ?) ORDER BY ? "+ascdesc+";");
		stm.setInt(1, blogID);
		stm.setInt(2, postID);
		result = stm.executeQuery();

		if (result.first()) {
			comments = new ArrayList<Comment>();
			result.beforeFirst();
			while(result.next()){
				comment = new Comment();
				comment.commentID = result.getInt("id");
				comment.blogID = result.getInt("blogID");
				comment.postID = result.getInt("postID");
				comment.commentator = result.getString("commentator");
				comment.comment = result.getString("comment");
				Calendar c = Calendar.getInstance();
				c.setTime(result.getTimestamp("addDate"));
				comment.addDate = c;
				
				comments.add(comment);
			}
		}
		connectionManager.recycleConnection(conn);
		return comments;
	}

	@Override
	public List<PostCategory> getAllPostCategoryList()	throws SQLException {
		List<PostCategory> postCategories = null;
		PostCategory postCat = null;
		
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn.prepareStatement("SELECT * FROM postcategories ORDER BY name ASC;");
		result = stm.executeQuery();

		if(result.first()){
			postCategories = new ArrayList<PostCategory>();
			result.beforeFirst();
			while (result.next()) {
				postCat = new PostCategory();
				postCat.id = result.getInt("id");
				postCat.name = result.getString("name");
				postCat.desc = result.getString("descr");
				postCat.blogCategoryId = result.getInt("blogCategoryId");
				
				postCategories.add(postCat);
			}
		}
		connectionManager.recycleConnection(conn);
		return postCategories;
	}

	@Override
	public List<PostCategory> getPostCategoryListOfBlog(Integer blogID)	throws SQLException {
		List<PostCategory> postCategories = null;
		PostCategory postCat = null;

		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();


		stm = conn
		.prepareStatement("SELECT * FROM postcategories WHERE(postcategories.blogCategoryId = (SELECT blogCategory FROM blogs WHERE blogs.id=?)) ORDER BY postcategories.name ASC;");
		stm.setInt(1, blogID);
		result = stm.executeQuery();

		if(result.first()){
			postCategories = new ArrayList<PostCategory>();
			result.beforeFirst();
			while (result.next()) {
				postCat = new PostCategory();
				postCat.id = result.getInt("id");
				postCat.name = result.getString("name");
				postCat.desc = result.getString("descr");
				postCat.blogCategoryId = result.getInt("blogCategoryId");
				
				postCategories.add(postCat);
			}
		}
		connectionManager.recycleConnection(conn);
		return postCategories;
	}

	@Override
	public Comment getComment(Integer commentID) throws SQLException {
		Comment comment = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn.prepareStatement("SELECT * FROM comments WHERE id = ?;");
		stm.setInt(1, commentID);
		result = stm.executeQuery();

		if (result.first()) {
			comment = new Comment();
			comment.commentID = result.getInt("id");
			comment.blogID = result.getInt("blogID");
			comment.postID = result.getInt("postID");
			comment.commentator = result.getString("commentator");
			comment.comment = result.getString("comment");
			
			Calendar c = Calendar.getInstance();
			c.setTime(result.getTimestamp("addDate"));
			comment.addDate = c;
			

		}
		connectionManager.recycleConnection(conn);
		return comment;
	}

	@Override
	public boolean createUser(User aUser) throws SQLException{
		int result = 0;

		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
			.prepareStatement("INSERT INTO users (name, surname, dateOfBirth, gender, username, password, role, state, email, imgURL, siteURL, blogURL, lastLogin, numLogin) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);");

			
		stm.setString(1, aUser.name);
		stm.setString(2, aUser.surname);
		java.sql.Date sqlDate = new java.sql.Date(aUser.dateOfBirth.getTime().getTime());
		stm.setDate(3,sqlDate);
		stm.setBoolean(4, aUser.gender);
		stm.setString(5, aUser.username);
		stm.setString(6, aUser.password);
		stm.setInt(7, aUser.role);
		stm.setBoolean(8, aUser.state);
		stm.setString(9, aUser.email);
		stm.setString(10, aUser.imgURL);
		stm.setString(11, aUser.siteURL);
		stm.setString(12, aUser.blogURL);
		Timestamp lastLogin = new Timestamp(System.currentTimeMillis());
		stm.setTimestamp(13, lastLogin);
		stm.setInt(14, 0);
	
		result = stm.executeUpdate();
		
		
		if (result > 0) {
			return true;
		} else {
			return false;
		}

	}

	@Override
	public boolean createBlog(Blog aBlog) throws SQLException {

		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("INSERT INTO blogs (blogTitle, blogOwner, blogCategory, counterBlogView, imgURL) VALUES (?, ?, ?, ?, ?);");

		stm.setString(1, aBlog.blogTitle);
		stm.setInt(2, aBlog.blogOwner);
		stm.setInt(3, aBlog.blogCategory);
		stm.setInt(4, 0);
		stm.setString(5, aBlog.imgURL);
		result = stm.executeUpdate();

		
		if (result > 0) {
			stm = conn.prepareStatement("UPDATE users SET blogURL=? WHERE userID= ?;");
			stm.setString(1, ""+aBlog.blogOwner+"");
			stm.setInt(2, aBlog.blogOwner);
			
			result = stm.executeUpdate();
			if (result > 0) 
				return true;
			else
				return false;
		} else {
			return false;
		}
	}

	@Override
	public boolean addPost(Post aPost) throws SQLException {

		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("INSERT INTO posts (blogID, title, content, Writer, imgURL, category, addDate, counterPostView) VALUES (?, ?, ?, ?, ?, ?, ?, ?);");
		stm.setInt(1, aPost.blogID);
		stm.setString(2, aPost.title);
		stm.setString(3, aPost.content);
		stm.setInt(4, aPost.writer);
		stm.setString(5, aPost.imgURL);
		stm.setInt(6, aPost.category);
	    java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(aPost.date.getTime().getTime());
		stm.setTimestamp(7,sqlTimestamp);
		stm.setInt(8, 0);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean addComment(Comment aComment) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("INSERT INTO comments (blogID, postID, commentator, comment, addDate) VALUES (?, ?, ?, ?, ?);");

		stm.setInt(1, aComment.blogID);
		stm.setInt(2, aComment.postID);
		stm.setString(3, aComment.commentator);
		stm.setString(4, aComment.comment);
	    java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(aComment.addDate.getTime().getTime());
		stm.setTimestamp(5,sqlTimestamp);
		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean addBlogCategory(BlogCategory aCategory) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("INSERT INTO blogcategories (name, descr) VALUES (?, ?);");
		stm.setString(1, aCategory.name);
		stm.setString(1, aCategory.desc);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean addPostCategory(PostCategory aCategory) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("INSERT INTO postcategories (name, descr,blogCategoryId) VALUES (?, ?, ?);");
		stm.setString(1, aCategory.name);
		stm.setString(2, aCategory.desc);
		stm.setInt(3,aCategory.blogCategoryId);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean updateUser(User user) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("UPDATE users SET name=?,surname=?,dateOfBirth=?,gender=?,username=?,password=?,role=?,state=?,email=?,imgURL=?,siteURL=?,blogURL=? WHERE userID= ?;");
		
		stm.setString(1, user.name);
		stm.setString(2, user.surname);
		java.sql.Date sqlDate = new java.sql.Date(user.dateOfBirth.getTime().getTime());	    
		stm.setDate(3, sqlDate);
		stm.setBoolean(4, user.gender);
		stm.setString(5, user.username);
		stm.setString(6, user.password);
		stm.setInt(7, user.role);
		stm.setBoolean(8, user.state);
		stm.setString(9, user.email);
		stm.setString(10, user.imgURL);
		stm.setString(11, user.siteURL);
		stm.setString(12, user.blogURL);
		stm.setInt(13, user.userID);
		result = stm.executeUpdate();
		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean updateBlog(Blog blog) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		
		stm = conn
		.prepareStatement("UPDATE blogs SET blogTitle=?,blogOwner=?,blogCategory=?,counterBlogView=?,imgURL=? WHERE id = ?;");

		stm.setString(1, blog.blogTitle);
		stm.setInt(2, blog.blogOwner);
		stm.setInt(3, blog.blogCategory);
		stm.setInt(4, blog.counterBlogView);
		stm.setString(5,blog.imgURL);
		stm.setInt(6, blog.blogID);
		result = stm.executeUpdate();
		

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean updatePost(Post post) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("UPDATE posts SET blogID=?, title=?, content=?, Writer=?, imgURL=?, category=?, addDate=?, counterPostView=? WHERE id = ?;");
		stm.setInt(1, post.blogID);
		stm.setString(2, post.title);
		stm.setString(3, post.content);
		stm.setInt(4, post.writer);
		stm.setString(5, post.imgURL);
		stm.setInt(6, post.category);
	    java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(post.date.getTime().getTime());
		stm.setTimestamp(7,sqlTimestamp);
		stm.setInt(8, post.counterPostView);
		stm.setInt(9, post.postID);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean updateComment(Comment comment) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("UPDATE comments SET blogID=? ,postID=?,commentator=?,comment=?,addDate=? WHERE comments.id=?;");
		stm.setInt(1, comment.blogID);
		stm.setInt(2, comment.postID);
		stm.setString(3, comment.commentator);
		stm.setString(4, comment.comment);
	    java.sql.Timestamp sqlTimestamp = new java.sql.Timestamp(comment.addDate.getTime().getTime());
		stm.setTimestamp(5,sqlTimestamp);
		stm.setInt(6, comment.commentID);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean updateBlogCategory(BlogCategory aCategory)
	throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("UPDATE blogcategories SET name=? ,desc=? WHERE blogcategories.id=?;");
		stm.setString(1, aCategory.name);
		stm.setString(2, aCategory.desc);
		stm.setInt(3, aCategory.id);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean updatePostCategory(PostCategory aCategory)
	throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("UPDATE postcategories SET name=? ,desc=?,blogCategoryId=?  WHERE postcategories.id=?;");
		stm.setString(1, aCategory.name);
		stm.setString(2, aCategory.desc);
		stm.setInt(3, aCategory.blogCategoryId);
		stm.setInt(4, aCategory.id);
		

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean removeUser(Integer userID) throws SQLException {

		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn.prepareStatement("DELETE FROM users WHERE userID=?;");
		stm.setInt(1, userID);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean removeBlog(Integer blogID) throws SQLException {
		int result = 0;
		int result1 = 0;

		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		
		stm = conn.prepareStatement("UPDATE users SET blogURL=? WHERE userID=?;");
		stm.setString(1, null);
		stm.setInt(2, getBlog(blogID).blogOwner);
		result1 = stm.executeUpdate();
		
		
		stm = conn.prepareStatement("DELETE FROM blogs WHERE id=?;");
		stm.setInt(1, blogID);
		result = stm.executeUpdate();
		

		
		
		if (result > 0 && result1 > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean removePost(Integer postID) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn.prepareStatement("DELETE FROM posts WHERE id=?;");
		stm.setInt(1, postID);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean removeComment(Integer commentID) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn.prepareStatement("DELETE FROM comments WHERE id=?;");
		stm.setInt(1, commentID);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean removeBlogCategory(Integer categoryID) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("DELETE FROM blogcategories WHERE id=?;");
		stm.setInt(1, categoryID);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public boolean removePostCategory(Integer categoryID) throws SQLException {
		int result = 0;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("DELETE FROM postcategories WHERE id=?;");
		stm.setInt(1, categoryID);

		result = stm.executeUpdate();

		if (result > 0) {
			return true;
		} else {
			return false;
		}
	}

	@Override
	public User login(String username, String password) throws SQLException {
		User user = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn
		.prepareStatement("SELECT * FROM users WHERE username=? AND password=?;");
		stm.setString(1, username);
		stm.setString(2, password);
		result = stm.executeQuery();

		if (result.first()) {

			user = new User();

			user.userID = result.getInt("userID");
			user.name = result.getString("name");
			user.surname = result.getString("surname");
			user.gender = result.getBoolean("gender");
			
			Calendar c = Calendar.getInstance();
			c.setTime(result.getDate("dateOfBirth"));
			user.dateOfBirth = c;
			
			user.email = result.getString("email");
			user.username = result.getString("username");
			user.password = result.getString("password");
			user.state = result.getBoolean("state");
			user.role = result.getInt("role");
			user.siteURL = result.getString("siteURL");
			user.blogURL = result.getString("blogURL");
			user.imgURL = result.getString("imgURL");

			int numLogin = result.getInt("numLogin") + 1;

			// TODO Bisogna inserire l'update automatico di lastlogin quando
			// effettuo
			// la query di seguito.

			stm = conn
			.prepareStatement("UPDATE users SET numlogin=? WHERE userID=?;");
			stm.setInt(1, numLogin);
			stm.setInt(2, user.userID);
			stm.executeUpdate();
			
		}
		connectionManager.recycleConnection(conn);
		return user;
	}

	@Override
	public List<TypeOfUsers> getUserRoles() throws SQLException {
		List<TypeOfUsers> roles = null;
		TypeOfUsers role = null;
		
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		stm = conn.prepareStatement("SELECT * FROM typeofusers;");
		result = stm.executeQuery();

		if (result.first()) {
			roles = new ArrayList<TypeOfUsers>();
			result.beforeFirst();
			while (result.next()) {
				role = new TypeOfUsers();
				role.id = result.getInt("id");
				role.name = result.getString("name");
				role.descProfile = result.getString("descrProfile");
				roles.add(role);
			}
		}
		connectionManager.recycleConnection(conn);
		return roles;
	}
	
	@Override
	public List<Blog> getBlogsOfCategory(Integer categoryId,String order,Boolean descending) throws SQLException {
		
		List<Blog> blogs = null;
		Blog blog = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		String ascdesc;
		if(descending)
			ascdesc="DESC";
		else
			ascdesc="ASC";

			stm = conn.prepareStatement("SELECT * FROM blogs WHERE blogCategory=? ORDER BY "+order+" "+ascdesc+";");

		stm.setInt(1, categoryId);
		result = stm.executeQuery();

		if (result.first()) {
			blogs = new ArrayList<Blog>();
			result.beforeFirst();
			while (result.next()) {
				blog = new Blog();
				blog.blogID = result.getInt("id");
				blog.blogTitle = result.getString("blogTitle");
				blog.blogOwner = result.getInt("blogOwner");
				blog.blogCategory = result.getInt("blogCategory");
				blog.counterBlogView = result.getInt("counterBlogView");
				blog.imgURL = result.getString("imgURL");
				blogs.add(blog);
			}
		}

		connectionManager.recycleConnection(conn);
		return blogs;
	}
	
	@Override
	public List<Post> getPostsOfCategory(Integer categoryId,Integer blogId,String order,Boolean descending)throws SQLException  {

		List<Post> posts = null;
		Post aPost = null;
		ResultSet result;
		PreparedStatement stm;
		Connection conn = connectionManager.getConnection();
		String ascdesc;
		if(descending)
			ascdesc="DESC";
		else
			ascdesc="ASC";
		stm = conn.prepareStatement("SELECT * FROM posts WHERE (blogID = ?) AND (category = ?) ORDER BY "+order+" "+ascdesc+";");
		stm.setInt(1, blogId);
		stm.setInt(2, categoryId);
		result = stm.executeQuery();

		if(result.first()) {
			posts = new ArrayList<Post>();
			result.beforeFirst();
			while(result.next()){
				aPost = new Post();
				aPost.postID = result.getInt("id");
				aPost.blogID = result.getInt("blogID");
				aPost.title = result.getString("title");
				aPost.content = result.getString("content");
				aPost.writer = result.getInt("Writer");
				aPost.imgURL = result.getString("imgURL");
				aPost.category = result.getInt("category");
				
				Calendar c = Calendar.getInstance();
				c.setTime(result.getTimestamp("addDate"));
				aPost.date = c;
				
				aPost.counterPostView = result.getInt("counterPostView");
				
				
				posts.add(aPost);
			}			
		}
		connectionManager.recycleConnection(conn);
		return posts;

		
		
	}

}
